use lopdf::Document;
use shq_common::prelude::{get_my_pool, CreditReportAbnormal, DpidUser, TransactionAbnormal};
use chrono::{DateTime, Duration, Local, NaiveDate, TimeZone};
use sqlx::{Error, Row};
use std::collections::HashMap;
use fancy_regex::Regex;


//获取部门下员工
pub async fn get_user_by_dpid(dpid: u32) -> anyhow::Result<Vec<DpidUser>> {
    let sql = "SELECT id as emid,name FROM  v_user_name WHERE dpid = ?";
    // 执行SQL语句，获取查询结果
    let rows = sqlx::query(sql)
        .bind(dpid)
        .fetch_all(get_my_pool())
        .await?;
    let mut users = Vec::new();
    for row in rows.iter() {
        let emid: u32 = row.try_get("emid")?;
        let name: String = row.try_get("name")?;
        let user = DpidUser { emid:emid,name:name };
        users.push(user);
    }
    Ok(users)
}
//保存家访员工
pub async fn save_home_visit_user(emids: Vec<u32>,dpid: u32,frid: u32) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    // 执行SQL语句，获取查询结果
    for emid in emids {
        let sql = "insert into hr_home_visit_empl(hvid,emid,emno,emnm,bfid1,bfid2,dpid) values (?,?,?,?,?,?,?)";
        sqlx::query(sql)
            .bind(frid)
            .bind(emid)
            .bind("")
            .bind("")
            .bind(0)
            .bind(0)
            .bind(dpid)
            .execute(&mut *tr)
            .await?;
        let sql2 = "update hr_home_visit_empl a inner join v_user_name b on a.emid  = b.id
                                  set a.emno = b.emno ,a.emnm = b.emnm
                                  where a.bfid1 = 0 and a.bfid2 = 0 and a.emid = ?";
        sqlx::query(sql2)
            .bind(emid)
            .execute(&mut *tr)
            .await?;
    }
    tr.commit().await?;
    Ok(())
}

pub async fn del_home_visit_user(id: u32) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    // 执行SQL语句，获取查询结果
    let sql1 = "delete from sys_binary_files where ID in (select bfid1 from hr_home_visit_empl where id = ?)";
    sqlx::query(sql1)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    let sql2 = "delete from sys_binary_files where ID in (select bfid2 from hr_home_visit_empl where id = ?)";
    sqlx::query(sql2)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    let sql = "delete from hr_home_visit_empl where id = ?";
    sqlx::query(sql)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn update_home_visit_fileid_to_user(id: u32,fileid: Vec<u64>) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "update hr_home_visit_empl set bfid1 = ? where id = ?";
    sqlx::query(sql)
        .bind(fileid[0])
        .bind(id)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn update_home_visit_image_fileid_to_user(id: u32,fileId: Vec<u64>) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "update hr_home_visit_empl set bfid2 = ? where id = ?";
    sqlx::query(sql)
        .bind(fileId[0])
        .bind(id)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn fetch_credit_report_abnormal() -> anyhow::Result<Vec<CreditReportAbnormal>> {
    let sql = "select id,name from hr_credit_report_abnormal";
    let rows = sqlx::query(sql)
        .fetch_all(get_my_pool())
        .await?;
    // let mut map = HashMap::with_capacity(rows.len());
    let mut creditreportabnormals = Vec::new();
    for row in rows.iter() {
        let id: u32 = row.try_get(0)?;
        let name: String = row.try_get(1)?;
        let creditreportabnormal = CreditReportAbnormal{id,name};
        creditreportabnormals.push(creditreportabnormal);
    }
    Ok(creditreportabnormals)
}

pub async fn fetch_transaction_abnormal() -> anyhow::Result<Vec<TransactionAbnormal>> {
    let sql = "select id,name from hr_transaction_abnormal";
    let rows = sqlx::query(sql)
        .fetch_all(get_my_pool())
        .await?;
    // let mut map = HashMap::with_capacity(rows.len());
    let mut transactionabnormals = Vec::new();
    for row in rows.iter() {
        let id: u32 = row.try_get(0)?;
        let name: String = row.try_get(1)?;
        let transaction_abnormal = TransactionAbnormal{id,name};
        transactionabnormals.push(transaction_abnormal);
    }
    Ok(transactionabnormals)
}

pub async fn save_credit_report_abnormal_record(
    ny: u32,
    frid: u32,
    emid: u32,
    emno: String,
    emnm: String,
    abnormal_ids: Vec<u64>,
) ->anyhow::Result<()>{
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "delete from hr_credit_report_abnormal_record where frid = ?";
    sqlx::query(sql)
        .bind(frid)
        .execute(&mut *tr)
        .await?;
    // 执行SQL语句，获取查询结果
    if abnormal_ids[0] != 0 {
        for abnormal_id in abnormal_ids {
            let sql = "insert into hr_credit_report_abnormal_record(ny,frid,emid,emno,emnm,abnormal_id) values (?,?,?,?,?,?)";
            sqlx::query(sql)
                .bind(ny)
                .bind(frid)
                .bind(emid)
                .bind(&emno)
                .bind(&emnm)
                .bind(abnormal_id)
                .execute(&mut *tr)
                .await?;
        }
        //更新流程状态为需要补录
        let sql = "update hr_credit_report_run set is_bccl = 1 where frid = ?";
        sqlx::query(sql)
        .bind(frid)
        .execute(&mut *tr)
        .await?;
    }else {
        //更新流程状态为不需要补录
        let sql = "update hr_credit_report_run set is_bccl = 0 where frid = ?";
        sqlx::query(sql)
        .bind(frid)
        .execute(&mut *tr)
        .await?;
    }
    tr.commit().await?;
    Ok(())
}

pub async fn save_transaction_abnormal_record(
    ny: u32,
    frid: u32,
    emid: u32,
    emno: String,
    emnm: String,
    abnormal_ids: Vec<u64>,
) ->anyhow::Result<()>{
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "delete from hr_transaction_abnormal_record where frid = ?";
    sqlx::query(sql)
        .bind(frid)
        .execute(&mut *tr)
        .await?;
    // 执行SQL语句，获取查询结果
    if abnormal_ids[0] != 0 {
        for abnormal_id in abnormal_ids {
            let sql = "insert into hr_transaction_abnormal_record(ny,frid,emid,emno,emnm,abnormal_id) values (?,?,?,?,?,?)";
            sqlx::query(sql)
                .bind(ny)
                .bind(frid)
                .bind(emid)
                .bind(&emno)
                .bind(&emnm)
                .bind(abnormal_id)
                .execute(&mut *tr)
                .await?;
        }
        //更新流程状态为需要补录
        let sql = "update hr_transaction_run set is_bccl = 1 where frid = ?";
        sqlx::query(sql)
        .bind(frid)
        .execute(&mut *tr)
        .await?;
    }else {
        //更新流程状态为需要补录
        let sql = "update hr_transaction_run set is_bccl = 0 where frid = ?";
        sqlx::query(sql)
        .bind(frid)
        .execute(&mut *tr)
        .await?;
    };
    tr.commit().await?;
    Ok(())
}

pub async fn save_transaction_file(
    ny: u32,
    frid: u32,
    emid: u32,
    emno: String,
    emnm: String,
    fileid: Vec<u64>,
) ->anyhow::Result<()>{
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    // 执行SQL语句，获取查询结果
    let sql = "insert into hr_transaction_file(ny,frid,emid,emno,emnm,file_id) values (?,?,?,?,?,?)";
    sqlx::query(sql)
        .bind(ny)
        .bind(frid)
        .bind(emid)
        .bind(&emno)
        .bind(&emnm)
        .bind(fileid[0])
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn get_abnormal_record_by_frid(frid: u32) -> anyhow::Result<Vec<u32>> {

    let sql = "select distinct abnormal_id  from hr_credit_report_abnormal_record where frid = ?";
    // 执行SQL语句，获取查询结果
    let rows = sqlx::query(sql)
        .bind(frid)
        .fetch_all(get_my_pool())
        .await?;
    let mut abnormals = Vec::<u32>::new();
    for row in rows.iter() {
        let abnormal_id: u32 = row.try_get("abnormal_id")?;      
        abnormals.push(abnormal_id);
    }
    Ok(abnormals)
}

pub async fn get_abnormal_transaction_by_frid(frid: u32) -> anyhow::Result<Vec<u32>> {

    let sql = "select distinct abnormal_id  from hr_transaction_abnormal_record where frid = ?";
    // 执行SQL语句，获取查询结果
    let rows = sqlx::query(sql)
        .bind(frid)
        .fetch_all(get_my_pool())
        .await?;
    let mut abnormals = Vec::<u32>::new();
    for row in rows.iter() {
        let abnormal_id: u32 = row.try_get("abnormal_id")?;      
        abnormals.push(abnormal_id);
    }
    Ok(abnormals)
}


pub async fn update_credit_report_fileid_by_frid(
    ny: u32,
    frid: u32,
    emid: u32,
    emno: String,
    emnm: String,
    fileids: Vec<u64>,
) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "update hr_credit_report_run set file_id = ? where frid = ?";
    sqlx::query(sql)
        .bind(fileids[0])
        .bind(frid)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    let abnormals = parse_credit_report_file_vecu8(fileids).await?;
    if abnormals.len() > 0 {
        save_credit_report_abnormal_record(ny, frid, emid, emno, emnm, abnormals).await?;
    }
    Ok(())
}
#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord)]
struct QueryRecord {
    number: u32,
    query_date: String,
    query_institution: String,
    query_reason: String,
}
async fn parse_credit_report_file_vecu8(fileids: Vec<u64>) -> anyhow::Result<Vec<u64>> {
    let sql = "SELECT file_data FROM sys_binary_files where id = ?";
    let row = sqlx::query(sql)
        .bind(fileids[0])
        .fetch_one(get_my_pool())
        .await?;
    let filedata: Vec<u8> = row.try_get(0).unwrap();

    //消费贷余额记录
    let mut consumer_loans: HashMap<String, i32> = HashMap::new();
    //发生过逾期的账户数,超过1次(含1次)
    let mut overdue_accounts = 0;
    //机构查询记录次数
    let mut query_records: Vec<QueryRecord> = Vec::new();
    //相关还款责任账户数
    let mut related_repayment_accounts = 0;

    //读取文件获取内容
    let document = Document::load_mem(&filedata)?;
    let page_ids:Vec<_> = document.get_pages().keys().cloned().collect();

    let page_num_re = Regex::new(r"第\s+\d+\s+页，共").unwrap();
    //消费贷余额
    let re = Regex::new(r"([^\d]+?)为其他个人消费贷款授信.*?余额为(\d+)").unwrap();
    //发生过逾期的账户数
    let number_re = Regex::new(r"(\d+)").unwrap();
    // 定义需要关注的查询类型
    let target_reasons = ["贷款审批", "信用卡审批", "担保资格审查"];
    // 计算当前日期和三个月前的日期
    let now = Local::now();
    let three_months_ago = now - Duration::days(90);
    // 用于匹配相关还款责任账户数的正则表达式
    let accounts_re = Regex::new(r"相关还款责任账户数\s*\n\s*--\s*\n\s*(\d+)").unwrap();

    for (index,page_id) in page_ids.iter().enumerate(){
        // 提取文本内容
        match document.extract_text(&[*page_id]) {
            Ok(text) if !text.trim().is_empty() => {
                // 清理文本中的页码信息
                let cleaned_text = page_num_re.replace_all(&text, "").trim().to_string();
                // 获取消费贷余额所有记录
                for result in re.captures_iter(&cleaned_text) {
                    match result {
                        Ok(cap)=>{
                            // 提取机构名称并去除可能的空格
                            let institution = cap[1].trim().to_string();
                            // 提取余额并转换为整数
                            let balance: i32 = cap[2].parse().unwrap_or(0);
                            // 将结果存入HashMap
                            consumer_loans.insert(institution, balance);
                        }
                        Err(e)=>{
                            eprintln!("提取消费贷余额失败: {}", e);
                        }
                    }
                }
                //发生过逾期的账户数
                if let Some(start_idx) = cleaned_text.find("发生过逾期的账户数") {
                    // 截取从关键词之后到下一个主要标题之前的内容
                    let remaining_text = &cleaned_text[start_idx + "发生过逾期的账户数".len()..];
                    let end_idx = remaining_text.find("发生过逾期的信用卡账户")
                        .unwrap_or(remaining_text.len());
                    let target_text = &remaining_text[..end_idx];
                    // 检查是否有数字
                    let result = number_re.is_match(target_text);
                    match result {
                        Ok(has_numbers)=>{
                            if has_numbers {
                                // 累加所有数字
                                for result in number_re.captures_iter(target_text) {
                                    match result {
                                        Ok(num)=>{
                                            if let Ok(num) = num[1].parse::<i32>() {
                                                overdue_accounts += num;
                                            }
                                        }
                                        Err(e) => {
                                            eprintln!("提取数字失败: {}", e);
                                        }
                                    }
                                }
                            } else {
                                // 没有数字时，检查是否有--，如果有则视为0
                                if target_text.contains("--") {
                                    overdue_accounts = 0;
                                }
                            }
                        }
                        Err(e)=>{
                            eprintln!("检查是否有数字失败: {}", e);
                        }
                    }
                }
                //机构查询记录次数
                if let Some(start_idx) = cleaned_text.find("机构查询记录明细") {
                    // 获取查询记录部分的文本
                    let query_section = &cleaned_text[start_idx..];
                    
                    // 使用正则表达式提取查询记录
                    let query_re = Regex::new(r"(?m)^(\d+)\s+(\d{4}年\d{2}月\d{2}日)\s+([^\n]+?)\s+([^\n]+)$").unwrap();
                    
                    for result in query_re.captures_iter(query_section) {
                        match result {
                            Ok(cap)=>{
                                let record = QueryRecord {
                                    number: cap[1].parse().unwrap_or(0),
                                    query_date: cap[2].to_string(),
                                    query_institution: cap[3].trim().to_string(),
                                    query_reason: cap[4].trim().to_string(),
                                };
                                query_records.push(record);
                            }
                            Err(e)=>{
                                eprintln!("提取查询记录失败: {}", e);
                            }
                        }
                    }
                }
                //存在对外担保和经营性贷款，提取相关还款责任账户数
                match accounts_re.captures(&cleaned_text) {
                    Ok(Some(caps)) => {
                        if let Some(num_match) = caps.get(1) {
                            if let Ok(num) = num_match.as_str().parse::<u32>() {
                                related_repayment_accounts = num;
                            }
                        }
                    }
                    Ok(None) => {
                        // 如果没有匹配到特定格式，尝试逐行查找
                        if let Some(start_idx) = cleaned_text.find("相关还款责任账户数") {
                            let lines: Vec<&str> = cleaned_text[start_idx..].lines().collect();
                            
                            for line in lines.iter().skip(1) {
                                let trimmed = line.trim();
                                if trimmed == "--" {
                                    related_repayment_accounts = 0;
                                    break;
                                } else if let Ok(num) = trimmed.trim_end_matches('.').parse::<u32>() {
                                    related_repayment_accounts = num;
                                    break;
                                }
                            }
                        }
                    }
                    Err(e) => {
                        eprintln!("提取相关还款责任账户数失败: {}", e);
                    }
                }
            }
            Ok(_) => {
                continue;
            }
            Err(e) => {
                println!("提取文本失败: {}", e);
            }
        }
    }
    //近三个月内查询类型为“贷款审批、信用卡审批、担保资格审查”超过5次以上
    let datetime_re = Regex::new(r"(\d{4})年(\d{2})月(\d{2})日").unwrap();
    let mut filtered_records = Vec::new();
    for record in &query_records {
        let result = datetime_re.captures(&record.query_date);
        match result {
            Ok(caps) => {
                match caps {
                    Some(cap) => {
                        let year = cap[1].parse::<i32>().unwrap();
                        let month = cap[2].parse::<u32>().unwrap();
                        let day = cap[3].parse::<u32>().unwrap();
                        
                        if let Some(naive_date) = NaiveDate::from_ymd_opt(year, month, day) {
                            let datetime: DateTime<Local> = Local.from_local_datetime(
                                &naive_date.and_hms_opt(0, 0, 0).unwrap()
                            ).unwrap();
                            if datetime >= three_months_ago && datetime <= now {
                                // 检查是否为目标查询类型
                                if target_reasons.contains(&record.query_reason.as_str()) {
                                    filtered_records.push(record);
                                }
                            }
                        }
                    }
                    None => {
                        eprintln!("解析日期失败");
                    }
                }
            }
            Err(e) => {
                eprintln!("解析日期失败: {}", e);
            }
        }
    }

    let mut abnormal_ids = Vec::<u64>::new();
    //余额总数大于300000
    let mut balance_sum = 0;
    for (_, balance) in consumer_loans {
        balance_sum+=balance;
    }
    if balance_sum > 300000 {
        abnormal_ids.push(1);
    }
    //发生过逾期的账户数大于0
    if overdue_accounts > 0 {
        abnormal_ids.push(2);
    }
    //机构查询记录次数大于等于50
    if query_records.len() >= 50 {
        abnormal_ids.push(3);
    }
    //近三个月内查询类型为“贷款审批、信用卡审批、担保资格审查”大于5
    if filtered_records.len() > 5 {
        abnormal_ids.push(4);
    }
    //相关还款责任账户数大于0
    if related_repayment_accounts > 0 {
        abnormal_ids.push(5);
    }
    Ok(abnormal_ids)
}

pub async fn save_credit_report_image_fileid_by_frid(
    ny: u32,
    frid: u32,
    emid: u32,
    emno: String,
    emnm: String,
    fileid: Vec<u64>,
) ->anyhow::Result<()>{
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    // 执行SQL语句，获取查询结果
    let sql = "insert into hr_credit_report_image_file(ny,frid,emid,emno,emnm,file_id) values (?,?,?,?,?,?)";
    sqlx::query(sql)
        .bind(ny)
        .bind(frid)
        .bind(emid)
        .bind(&emno)
        .bind(&emnm)
        .bind(fileid[0])
        .execute(&mut *tr)
        .await?;  
    tr.commit().await?;
    Ok(())
}

pub async fn get_credit_report_image_by_frid(frid: u32) -> anyhow::Result<HashMap<u64, u64>> {
    let sql = "select distinct file_id from hr_credit_report_image_file where frid = ?";
    // 执行SQL语句，获取查询结果
    let rows = sqlx::query(sql)
        .bind(frid)
        .fetch_all(get_my_pool())
        .await?;
    let mut image_map = HashMap::with_capacity(rows.len());
    let mut count: u64 = 0;
    for row in rows.iter() {
        let image_file_id: u64 = row.try_get("file_id")?;
        count +=1 ;
        image_map.insert(count, image_file_id);
    }
    Ok(image_map)
}

pub async fn get_transaction_file_by_frid(frid: u32)-> anyhow::Result<HashMap<u64, u64>> {
    let sql = "select distinct file_id from hr_transaction_file where frid = ?";
    // 执行SQL语句，获取查询结果
    let rows = sqlx::query(sql)
        .bind(frid)
        .fetch_all(get_my_pool())
        .await?;
    let mut file_map = HashMap::with_capacity(rows.len());
    let mut count: u64 = 0;
    for row in rows.iter() {
        let file_id: u64 = row.try_get("file_id")?;
        count +=1 ;
        file_map.insert(count, file_id);
    }
    Ok(file_map)
}

pub async fn del_transaction_file_by_fileid(id: u64) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "delete from hr_transaction_file where file_id = ?";
    sqlx::query(sql)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    let sql2 = "delete from sys_binary_files where id = ?";
    sqlx::query(sql2)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn del_credit_report_image_by_fileid(id: u64) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "delete from hr_credit_report_image_file where file_id = ?";
    sqlx::query(sql)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    let sql2 = "delete from sys_binary_files where id = ?";
    sqlx::query(sql2)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn del_credit_report_file_by_fileid(id: u32,frid: u32) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "update hr_credit_report_run set file_id = 0 where frid = ?";
    sqlx::query(sql)
        .bind(frid)
        .execute(&mut *tr)
        .await?;
    let sql2 = "delete from sys_binary_files where id = ?";
    sqlx::query(sql2)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn del_home_visit_file_by_fileid(id: u32,home_id: u32) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "update hr_home_visit_empl set bfid1 = 0 where id = ?";
    sqlx::query(sql)
        .bind(home_id)
        .execute(&mut *tr)
        .await?;
    let sql2 = "delete from sys_binary_files where id = ?";
    sqlx::query(sql2)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn del_home_visit_image_by_fileid(id: u32,home_id: u32) -> anyhow::Result<()> {
    let pool = get_my_pool();
    let mut tr = pool.begin().await?;
    let sql = "update hr_home_visit_empl set bfid2 = 0 where id = ?";
    sqlx::query(sql)
        .bind(home_id)
        .execute(&mut *tr)
        .await?;
    let sql2 = "delete from sys_binary_files where id = ?";
    sqlx::query(sql2)
        .bind(id)
        .execute(&mut *tr)
        .await?;
    tr.commit().await?;
    Ok(())
}

pub async fn get_flow_run_step(frid: u32,emid: u32)-> anyhow::Result<u32> {

    if frid == 0 || emid == 0 {
        return Ok(0);
    }
    let sql = "SELECT step_id FROM flow_run_step WHERE run_id = ? and emid = ? and submit_time is null";
    let rows = sqlx::query(sql)
    .bind(frid)
    .bind(emid)
    .fetch_one(get_my_pool())
    .await?;

    let step_id: u32 = rows.try_get("step_id")?;
    Ok(step_id)
}
